package com.zyd.service.stat.impl;

import com.google.common.collect.Maps;
import com.zyd.context.HeaderContext;
import com.zyd.io.model.*;
import com.zyd.io.page.PageMapBean;
import com.zyd.io.page.PageRequestBean;
import com.zyd.mappers.CountyEntityMapper;
import com.zyd.mappers.StationEntityMapper;
import com.zyd.mappers.VehicleEntityMapper;
import com.zyd.service.power.PowerService;
import com.zyd.service.stat.StatService;
import com.zyd.util.SqlUtils;
import com.zyd.util.ToolUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Component
public class StatServiceImpl implements StatService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public PageMapBean queryNoRecord(PageRequestBean request) {
        String tagCode = request.getString("tagcode");
        String vinno = request.getString("vinno");
        int datenum = request.getInt("datenum", 3);

        String countsql = " SELECT count(*) ";
        String selectfield = " SELECT a.tagcode,a.carname,a.mobile,a.vinno ";
        StringBuffer sql = new StringBuffer();
        sql.append(" FROM zydzn_vehicle  a ");

        if (datenum <= 3) {
            sql.append(" LEFT JOIN ( SELECT  distinct b.tag_id FROM socket_now b  WHERE b.event_type='1000' and ");
            if (datenum == 0) {
                sql.append(" (date_sub(curdate(), INTERVAL 1 DAY)  <= date(b.rec_time))  ");
            } else {
                sql.append(" ( date_sub(curdate(), INTERVAL " + datenum + " DAY)  <= date(b.rec_time) ) ");
            }
        } else {
            sql.append(" LEFT JOIN  ( SELECT  distinct b.tag_id FROM socket_history_thirty b  WHERE b.event_type='1000' and ");
            sql.append(" ( date_sub(curdate(), INTERVAL " + datenum + " DAY)  <= date(b.rec_time) ) ");
        }
        if (!ToolUtils.isEmpty(tagCode)) {
            sql.append("  and  b.tag_id = '" + tagCode + "'");
        }
        sql.append("    ) b on a.tagcode = b.tag_id  WHERE  a.dr = 0 and b.tag_id is null ");
        if (!ToolUtils.isEmpty(tagCode)) {
            sql.append("  and  a.tagcode = '" + tagCode + "'");
        }
        if (!ToolUtils.isEmpty(vinno)) {
            sql.append("  and  a.vinno = '" + vinno + "'");
        }

        Long totalRows = jdbcTemplate.queryForObject(countsql + sql.toString(), Long.class);

        String pageSql = SqlUtils.addPage(selectfield + sql.toString(), request.getCurrentPage(), request.getPageSize());

        List<Map<String, Object>> retList = jdbcTemplate.query(pageSql, new RowMapper<Map<String, Object>>() {

            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> ret = Maps.newHashMap();
                ret.put("tagcode", rs.getString("tagcode"));
                ret.put("carname", rs.getString("carname"));
                ret.put("mobile", rs.getString("mobile"));
                ret.put("vinno", rs.getString("vinno"));
                return ret;
            }
        });

        PageMapBean ret = new PageMapBean(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        ret.setItems(retList);
        return ret;
    }

    public PageMapBean querySocketHistory30(PageRequestBean request) {
        return doQuerySocketHistory(request, "socket_history_thirty");
    }


    public PageMapBean querySocketHistory60(PageRequestBean request) {
        return doQuerySocketHistory(request, "socket_history_sixty");
    }

    public PageMapBean querySocketHistory90(PageRequestBean request) {
        return doQuerySocketHistory(request, "socket_history_ninety");
    }

    public PageMapBean querySocketHistory180(PageRequestBean request) {
        return doQuerySocketHistory(request, "socket_history_halfyear");
    }

    public PageMapBean querySocketHistory360(PageRequestBean request) {
        return doQuerySocketHistory(request, "socket_history_year");
    }

    /**
     * 根据时间段查询各个地区的安装数量
     *
     * @param map
     * @return
     */
    @Override
    public List installationStatistics(Map map) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd mm:hh:ss");
        Date startTime = new Date(System.currentTimeMillis() - 86400000L);
        Date endTime = new Date();
        String startTimeStr = (String) map.get("startTime");
        String endTimeStr = (String) map.get("endTime");
        if (StringUtils.isEmpty(startTimeStr)) {
            startTimeStr = df.format(startTime);
        }
        if (StringUtils.isEmpty(endTimeStr)) {
            endTimeStr = df.format(endTime);
        }
        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");
        CountyEntityExample example = new CountyEntityExample();
        example.createCriteria().andIdIn(Arrays.asList(rolePowerEntity.getArea().split(",")));
        List<CountyEntity> list = countyEntityMapper.selectByExample(example);
        String finalStartTimeStr = startTimeStr;
        String finalEndTimeStr = endTimeStr;
        List<Map<String, Object>> resultList = list.stream().map(countyEntity -> {
            Map<String, Object> hashMap = new HashMap<>();
            hashMap.put("id", countyEntity.getId());
            hashMap.put("name", countyEntity.getName());
            VehicleEntityExample vehicleExample = new VehicleEntityExample();
            StationEntityExample stationExample = new StationEntityExample();
            vehicleExample.createCriteria().andNewareaidEqualTo(countyEntity.getId()).andTsGreaterThanOrEqualTo(finalStartTimeStr).andTsLessThanOrEqualTo(finalEndTimeStr).andDrEqualTo(0);
            stationExample.createCriteria().andAreaEqualTo(countyEntity.getId()).andTsGreaterThanOrEqualTo(finalStartTimeStr).andTsLessThanOrEqualTo(finalEndTimeStr).andDrEqualTo(0);
            hashMap.put("vehicleCount", vehicleEntityMapper.countByExample(vehicleExample));
            hashMap.put("staionCount", stationEntityMapper.countByExample(stationExample));
            return hashMap;
        }).collect(Collectors.toList());
        return resultList;

    }

    @Autowired
    private StationEntityMapper stationEntityMapper;
    @Autowired
    private VehicleEntityMapper vehicleEntityMapper;
    @Autowired
    private CountyEntityMapper countyEntityMapper;
    @Autowired
    private PowerService powerService;

    private PageMapBean doQuerySocketHistory(PageRequestBean request, String tableName) {
        String stationcode = request.getString("stationcode");
        String tagcode = request.getString("tagcode");

        String startdate = request.getString("startTime");
        String enddate = request.getString("endTime");

        String countsql = " SELECT count(1)  ";
        StringBuffer swhere = new StringBuffer();
        String selectfield = " SELECT b.carname,a.tag_id as tagcode, a.base_id as stationcode,c.stationname," + "DATE_FORMAT(a.rec_time,'%Y-%m-%d %H:%i:%s') as regtime ,a.event_type as eventtype ";
        StringBuffer sql = new StringBuffer();
        sql.append(" from " + tableName + " a LEFT JOIN zydzn_vehicle b on b.tagcode=a.tag_id and b.dr=0 ");
        sql.append(" LEFT JOIN zydzn_station c on  c.stationcode=a.base_id  and c.dr=0 ");
        swhere.append(" where  a.rec_time  between  ");
        swhere.append(" DATE_FORMAT('" + startdate + "', '%Y-%m-%d %H:%i:%s') ");
        swhere.append("  and DATE_FORMAT('" + enddate + "', '%Y-%m-%d %H:%i:%s') ");

        if (!ToolUtils.isEmpty(tagcode)) {
            swhere.append("  and  a.tag_id = '" + tagcode + "'");
        }
        if (!ToolUtils.isEmpty(stationcode)) {
            swhere.append("  and a.base_id = '" + stationcode + "'");
        }
        swhere.append(" ORDER BY a.rec_time desc  ");

        Long totalRows = jdbcTemplate.queryForObject(countsql + sql.toString() + swhere.toString(), Long.class);

        String pageSql = SqlUtils.addPage(selectfield + sql.toString() + swhere.toString(), request.getCurrentPage(), request.getPageSize());

        List<Map<String, Object>> retList = jdbcTemplate.query(pageSql, new RowMapper<Map<String, Object>>() {

            @Override
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String, Object> ret = Maps.newHashMap();
                ret.put("carname", rs.getString("carname"));
                ret.put("tagcode", rs.getString("tagcode"));
                ret.put("stationcode", rs.getString("stationcode"));
                ret.put("stationname", rs.getString("stationname"));
                ret.put("regtime", rs.getString("regtime"));
                ret.put("eventtype", rs.getString("eventtype"));
                return ret;
            }
        });

        PageMapBean ret = new PageMapBean(request.getCurrentPage(), request.getPageSize(), totalRows.intValue());
        ret.setItems(retList);
        return ret;
    } /**
     * 根据网点查询网点数据
     *
     * @param map
     * @return
     */
    @Override
    public List salesiteStatistics(Map map) {
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd mm:hh:ss");
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd mm:hh:ss");
        Date date = new Date();
        String format = simpleDateFormat.format(date);
        map.get("");
        map.get("");
      

        Date startTime = new Date(System.currentTimeMillis() - 86400000L);
        Date endTime = new Date();
        String startTimeStr = (String) map.get("startTime");
        String endTimeStr = (String) map.get("endTime");
        if (StringUtils.isEmpty(startTimeStr)) {
            startTimeStr = df.format(startTime);
        }
        if (StringUtils.isEmpty(endTimeStr)) {
            endTimeStr = df.format(endTime);
        }
        RolePowerEntity rolePowerEntity = powerService.queryPowerListByUserId((String) HeaderContext.get().get("userid"), "2010");
        String finalStartTimeStr = startTimeStr;
        String finalEndTimeStr = endTimeStr;

        String  sql ="SELECT id,networkname  FROM zydzn_network WHERE dr=0";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        List<Map<String, Object>> collect = maps.stream().map(vehicleEntity -> {
            Map<String, Object> hashMap = new HashMap<>();
            hashMap.put("id",vehicleEntity.get("id"));
            hashMap.put("networkname",vehicleEntity.get("networkname"));
            hashMap.put("networkname",vehicleEntity.get("networkname"));



            VehicleEntityExample vehicleExample = new VehicleEntityExample();
            String id = ""+vehicleEntity.get("id");
            List<String> strings = Arrays.asList(rolePowerEntity.getArea().split(","));
            vehicleExample.createCriteria().andSalesiteEqualTo(id).andTsGreaterThanOrEqualTo(finalStartTimeStr).andTsLessThanOrEqualTo(finalEndTimeStr).andDrEqualTo(0).andNewareaidIn(Arrays.asList(rolePowerEntity.getArea().split(",")));
        hashMap.put("salesiteCount",vehicleEntityMapper.countByExample(vehicleExample));
            return hashMap;
        }).collect(Collectors.toList());
        return collect;

    }


}
